﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using CustomControlDatabaseInterface.Utilities;

namespace CustomControlDatabaseInterface.Model
{
    public class CustomerTechControls : DataBaseListBase<String> {

        private String CustomerName { get; set; }
        private String Technology { get; set; }

        public CustomerTechControls(String customerName, String currentTech) {
            CustomerName = customerName;
            Technology = currentTech;
            FillList();
        }

        protected override void FillList() {
            List<String> allControls = DatabaseUtil.GetTableHeaders(Technology);
            List<String> resultControls = new List<string>();
            List<String> customerControls = new List<string>();
            String rowName = CustomerName;
            String query = "USE " + DatabaseUtil.Database + "; ";
            query += "SELECT * FROM " + Technology + " WHERE `Kunde`='" + CustomerName + "';";
            customerControls = DatabaseUtil.GetDataRow(query);
            if (customerControls != null) {
                for (int i = 1; i < allControls.Count; i++)
                {
                    if (customerControls[i] != String.Empty && customerControls[i] != "\r")
                    {
                        if (customerControls[i].ToLower() != "x") {
                            resultControls.Add(customerControls[i]);
                        }
                        else {
                            resultControls.Add(allControls[i]);
                        }
                        
                    }
                }
            }
            List = resultControls; 
        }

        public override void UpdateList(List<String> newControls) {
            if (!TechnologyContainsCustomer()) {
                AddCustomerToTech();
            }
            List<String> actualNewControls = newControls.Except(List).ToList();
            List<String> notExistingNewControls = GetNewlyCreatedControls(actualNewControls); 
            if (notExistingNewControls.Count > 0) {
                CreateNewControlColumns(notExistingNewControls);
            }
            if (actualNewControls.Count > 0) {
                AddExistingControlsToDataBase(actualNewControls);
            }
        }

        private List<String> GetNewlyCreatedControls(List<String> controls)
        {
            List<String> existingControls = DatabaseUtil.GetTableHeaders(Technology);
            List<String> result = new List<string>();
            result = controls.Except(existingControls).ToList();
            return result;
        }

        private void AddExistingControlsToDataBase( List<String> existingControls) {

            String query = "USE " + DatabaseUtil.Database + "; ";
            query += "UPDATE " + Technology + " SET ";
            query += "`" + existingControls[0] + "`='x'";

            for (int i = 1; i < existingControls.Count; i++)
            {
                query += ", `" + existingControls[i] + "`='x'";
            }
            query += " WHERE Kunde='" + CustomerName + "';";
            DatabaseUtil.UpdateDataRow(query);
        }
        
        private void CreateNewControlColumns(List<String> newControls ) {
            string query = "Use " + DatabaseUtil.Database + "; ";
            foreach (String item in newControls) {
                query += "ALTER TABLE " + Technology + " ADD " + item + " VARCHAR(50); ";

            }
            DatabaseUtil.ExecuteNonQuery(query);
        }

        private void CreateNewTechnology() {
            string query = "Use " + DatabaseUtil.Database + ";";
            query += "CREATE TABLE IF NOT EXISTS `" + Technology + "` (`kunde` VARCHAR(50))";
            DatabaseUtil.ExecuteNonQuery(query);
        }

        private bool TechnologyContainsCustomer() {
            string query = "Use " + DatabaseUtil.Database + "; ";
            query += "SELECT kunde FROM " + Technology + " WHERE `kunde`='" + CustomerName + "';";
            List<String> customers = DatabaseUtil.GetDataColumn(query, "kunde");
            if (customers.Count < 1) return false;
            return true; 
        }

        private void AddCustomerToTech() {
            string query = "USE " + DatabaseUtil.Database + ";";
            query += "INSERT INTO " + Technology + " (kunde) VALUE ('"+CustomerName+"')";
            DatabaseUtil.ExecuteNonQuery(query);
        }


    }
}
